/*
Navicat SQL Server Data Transfer

Source Server         : LocalHost
Source Server Version : 100000
Source Host           : INFINITIV2\SQLEXPRESS:1433
Source Database       : iShop
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 100000
File Encoding         : 65001

Date: 2013-04-11 20:19:46
*/


-- ----------------------------
-- Table structure for [dbo].[categories]
-- ----------------------------
DROP TABLE [dbo].[categories]
GO
CREATE TABLE [dbo].[categories] (
[id] int NOT NULL IDENTITY(1,1) ,
[name] nvarchar(200) NOT NULL ,
[parent] int NOT NULL DEFAULT ((-1)) 
)


GO
DBCC CHECKIDENT(N'[dbo].[categories]', RESEED, 8)
GO

-- ----------------------------
-- Records of categories
-- ----------------------------
SET IDENTITY_INSERT [dbo].[categories] ON
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'2', N'Компьютеры', N'-1');
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'3', N'Мониторы', N'-1');
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'4', N'Ноутбуки', N'-1');
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'5', N'Системники', N'2');
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'6', N'Планшеты', N'2');
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'7', N'Бюджетные', N'4');
GO
INSERT INTO [dbo].[categories] ([id], [name], [parent]) VALUES (N'8', N'Игровые', N'4');
GO
SET IDENTITY_INSERT [dbo].[categories] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[products]
-- ----------------------------
DROP TABLE [dbo].[products]
GO
CREATE TABLE [dbo].[products] (
[id] int NOT NULL IDENTITY(1,1) ,
[name] nvarchar(200) NOT NULL ,
[price] money NOT NULL ,
[propertyGroup] int NOT NULL DEFAULT ((-1)) 
)


GO

-- ----------------------------
-- Records of products
-- ----------------------------
SET IDENTITY_INSERT [dbo].[products] ON
GO
SET IDENTITY_INSERT [dbo].[products] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[properties]
-- ----------------------------
DROP TABLE [dbo].[properties]
GO
CREATE TABLE [dbo].[properties] (
[id] int NOT NULL IDENTITY(1,1) ,
[name] nvarchar(200) NOT NULL 
)


GO

-- ----------------------------
-- Records of properties
-- ----------------------------
SET IDENTITY_INSERT [dbo].[properties] ON
GO
SET IDENTITY_INSERT [dbo].[properties] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[propertyGroups]
-- ----------------------------
DROP TABLE [dbo].[propertyGroups]
GO
CREATE TABLE [dbo].[propertyGroups] (
[id] int NOT NULL IDENTITY(1,1) ,
[name] nvarchar(200) NOT NULL 
)


GO

-- ----------------------------
-- Records of propertyGroups
-- ----------------------------
SET IDENTITY_INSERT [dbo].[propertyGroups] ON
GO
SET IDENTITY_INSERT [dbo].[propertyGroups] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[propertyValues]
-- ----------------------------
DROP TABLE [dbo].[propertyValues]
GO
CREATE TABLE [dbo].[propertyValues] (
[id] int NOT NULL IDENTITY(1,1) ,
[value] nvarchar(200) NOT NULL 
)


GO

-- ----------------------------
-- Records of propertyValues
-- ----------------------------
SET IDENTITY_INSERT [dbo].[propertyValues] ON
GO
SET IDENTITY_INSERT [dbo].[propertyValues] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[roles]
-- ----------------------------
DROP TABLE [dbo].[roles]
GO
CREATE TABLE [dbo].[roles] (
[id] int NOT NULL IDENTITY(1,1) ,
[roleName] nvarchar(200) NOT NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[roles]', RESEED, 3)
GO

-- ----------------------------
-- Records of roles
-- ----------------------------
SET IDENTITY_INSERT [dbo].[roles] ON
GO
INSERT INTO [dbo].[roles] ([id], [roleName]) VALUES (N'1', N'admin');
GO
INSERT INTO [dbo].[roles] ([id], [roleName]) VALUES (N'3', N'moderator');
GO
INSERT INTO [dbo].[roles] ([id], [roleName]) VALUES (N'2', N'user');
GO
SET IDENTITY_INSERT [dbo].[roles] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[sysdiagrams]
-- ----------------------------
DROP TABLE [dbo].[sysdiagrams]
GO
CREATE TABLE [dbo].[sysdiagrams] (
[name] sysname NOT NULL ,
[principal_id] int NOT NULL ,
[diagram_id] int NOT NULL IDENTITY(1,1) ,
[version] int NULL ,
[definition] varbinary(MAX) NULL 
)


GO

-- ----------------------------
-- Records of sysdiagrams
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sysdiagrams] ON
GO
SET IDENTITY_INSERT [dbo].[sysdiagrams] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[users]
-- ----------------------------
DROP TABLE [dbo].[users]
GO
CREATE TABLE [dbo].[users] (
[id] int NOT NULL IDENTITY(1,1) ,
[userName] nvarchar(200) NOT NULL ,
[password] nvarchar(200) NOT NULL ,
[createDate] date NOT NULL ,
[email] nvarchar(200) NOT NULL ,
[isBlocked] bit NOT NULL DEFAULT ((0)) ,
[isActivated] bit NOT NULL DEFAULT ((0)) 
)


GO
DBCC CHECKIDENT(N'[dbo].[users]', RESEED, 7)
GO

-- ----------------------------
-- Records of users
-- ----------------------------
SET IDENTITY_INSERT [dbo].[users] ON
GO
INSERT INTO [dbo].[users] ([id], [userName], [password], [createDate], [email], [isBlocked], [isActivated]) VALUES (N'1', N'asd', N'asd', N'2012-12-20', N'asd', N'0', N'1');
GO
INSERT INTO [dbo].[users] ([id], [userName], [password], [createDate], [email], [isBlocked], [isActivated]) VALUES (N'3', N'фыв', N'фыв', N'2012-12-20', N'фыв', N'0', N'0');
GO
SET IDENTITY_INSERT [dbo].[users] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[usersInRoles]
-- ----------------------------
DROP TABLE [dbo].[usersInRoles]
GO
CREATE TABLE [dbo].[usersInRoles] (
[userId] int NOT NULL ,
[roleId] int NOT NULL 
)


GO

-- ----------------------------
-- Records of usersInRoles
-- ----------------------------
INSERT INTO [dbo].[usersInRoles] ([userId], [roleId]) VALUES (N'1', N'1');
GO
INSERT INTO [dbo].[usersInRoles] ([userId], [roleId]) VALUES (N'1', N'2');
GO
INSERT INTO [dbo].[usersInRoles] ([userId], [roleId]) VALUES (N'3', N'3');
GO

-- ----------------------------
-- Procedure structure for [dbo].[CreateUser]
-- ----------------------------
DROP PROCEDURE [dbo].[CreateUser]
GO
CREATE PROCEDURE [dbo].[CreateUser]
	(
		@userName nvarchar(50),
		@password nvarchar(50),
		@email nvarchar(50),
		@isBlocked bit,
		@isActivated bit
	)
AS
	INSERT INTO Users 
			(userName, password, createDate, email, isBlocked, isActivated)
	VALUES (@userName, @password, SYSDATETIME(), @email, @isBlocked, @isActivated);
RETURN @@ROWCOUNT;

GO

-- ----------------------------
-- Procedure structure for [dbo].[GetRolesForUser]
-- ----------------------------
DROP PROCEDURE [dbo].[GetRolesForUser]
GO
CREATE PROCEDURE [dbo].[GetRolesForUser]
	(
		@userName nvarchar(200)
	)
AS
SELECT roles.roleName
FROM users
		INNER JOIN usersInRoles ON users.id = usersInRoles.userId
		INNER JOIN roles ON usersInRoles.roleId = roles.id
WHERE users.userName = @userName;

GO

-- ----------------------------
-- Procedure structure for [dbo].[GetUser]
-- ----------------------------
DROP PROCEDURE [dbo].[GetUser]
GO
CREATE PROCEDURE [dbo].[GetUser]
	(
		@userName nvarchar(200) = " ",
		@userId int = -1
	)
AS
IF @userId != -1
	SELECT id, userName, createDate, email, isBlocked, isActivated 
	FROM users 
	WHERE id = @userId;
ELSE
	SELECT id, userName, createDate, email, isBlocked, isActivated 
	FROM users 
	WHERE userName = @userName;


GO

-- ----------------------------
-- Procedure structure for [dbo].[IsUserInRole]
-- ----------------------------
DROP PROCEDURE [dbo].[IsUserInRole]
GO
CREATE PROCEDURE [dbo].[IsUserInRole]
	(
		@roleName nvarchar(200),
		@userName nvarchar(200)
	)
AS
SELECT roles.roleName
FROM users
		INNER JOIN usersInRoles ON users.id = usersInRoles.userId
		INNER JOIN roles ON usersInRoles.roleId = roles.id
WHERE roles.roleName = @roleName AND 
      users.userName = @userName;
GO

-- ----------------------------
-- Procedure structure for [dbo].[ValidateUser]
-- ----------------------------
DROP PROCEDURE [dbo].[ValidateUser]
GO
CREATE PROCEDURE [dbo].[ValidateUser]
	(
		@userName nvarchar(200),
		@password nvarchar(200)
	)
AS
SELECT userName
FROM users
WHERE userName = @userName AND
	  password = @password;

GO

-- ----------------------------
-- Indexes structure for table categories
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[categories]
-- ----------------------------
ALTER TABLE [dbo].[categories] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table products
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[products]
-- ----------------------------
ALTER TABLE [dbo].[products] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table properties
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[properties]
-- ----------------------------
ALTER TABLE [dbo].[properties] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table propertyGroups
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[propertyGroups]
-- ----------------------------
ALTER TABLE [dbo].[propertyGroups] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table propertyValues
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[propertyValues]
-- ----------------------------
ALTER TABLE [dbo].[propertyValues] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table roles
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[roles]
-- ----------------------------
ALTER TABLE [dbo].[roles] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Uniques structure for table [dbo].[roles]
-- ----------------------------
ALTER TABLE [dbo].[roles] ADD UNIQUE ([roleName] ASC)
GO

-- ----------------------------
-- Indexes structure for table sysdiagrams
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[sysdiagrams]
-- ----------------------------
ALTER TABLE [dbo].[sysdiagrams] ADD PRIMARY KEY ([diagram_id])
GO

-- ----------------------------
-- Uniques structure for table [dbo].[sysdiagrams]
-- ----------------------------
ALTER TABLE [dbo].[sysdiagrams] ADD UNIQUE ([principal_id] ASC, [name] ASC)
GO

-- ----------------------------
-- Indexes structure for table users
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[users]
-- ----------------------------
ALTER TABLE [dbo].[users] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Uniques structure for table [dbo].[users]
-- ----------------------------
ALTER TABLE [dbo].[users] ADD UNIQUE ([email] ASC)
GO
ALTER TABLE [dbo].[users] ADD UNIQUE ([userName] ASC)
GO

-- ----------------------------
-- Indexes structure for table usersInRoles
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[usersInRoles]
-- ----------------------------
ALTER TABLE [dbo].[usersInRoles] ADD PRIMARY KEY ([userId], [roleId])
GO
